﻿using ArielCore.MixedDataBase.MySql;
using Microsoft.AspNetCore.Http;
using Microsoft.EntityFrameworkCore;
using NewRetail.Application.Components.Goods.Entity;
using NewRetail.Application.Components.Goods.Model;
using NewRetail.Application.Components.Warehouse.Entity;
using NewRetail.Application.Core;
using NewRetail.Application.DataAccess;
using OfficeOpenXml;
using Serilog;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using Volo.Abp.Application.Services;
using Volo.Abp.Domain.Repositories;

namespace NewRetail.Application.Components.Warehouse
{
    public interface IDiscountService : IApplicationService
    {
        Task<PromoteSalesDiscount[]> AttachWarehouse(PromoteSalesDiscount[] data);
        Task<PromoteSalesDiscount[]> AttachGoodsCategory(PromoteSalesDiscount[] data);
        Task<PromoteSalesDiscount[]> AttachSingleGoods(PromoteSalesDiscount[] data);
        /// <summary>
        /// 单品打折商品导入
        /// </summary>
        /// <param name="formFile"></param>
        /// <param name="discountId"></param>
        /// <param name="warehouseId"></param>
        /// <returns></returns>
        Task<(bool, string)> ImportDiscountGoods(IFormFile formFile, string discountId, string warehouseId);
    }

    public class DiscountService : MyApplicationService, IDiscountService
    {
        private readonly IRepository<PromoteSalesDiscount> promoteSalesDiscounts;
        private readonly IMsSqlDbRepository<NewRetailDapperDbContext> _dapperRepository;

        public DiscountService(IRepository<PromoteSalesDiscount> promoteSalesDiscounts, IMsSqlDbRepository<NewRetailDapperDbContext> dapperRepository)
        {
            this.promoteSalesDiscounts = promoteSalesDiscounts;
            _dapperRepository = dapperRepository;
        }

        public async Task<PromoteSalesDiscount[]> AttachGoodsCategory(PromoteSalesDiscount[] data)
        {
            var db = await this.promoteSalesDiscounts.GetDbContextAsync();

            var ids = data.Select(x => x.Id).ToArray();

            var discountMap = await db.Set<PromoteSalesDiscountCategory>().AsNoTracking().Where(x => ids.Contains(x.discountID)).ToArrayAsync();
            await db.AttachData<GoodsCategory, PromoteSalesDiscountCategory>(discountMap, x => x.categoryID, (m, arr) => m.GoodsCategory = arr.FirstOrDefault());

            foreach (var m in data)
            {
                m.PromoteSalesDiscountCategory = discountMap.Where(x => x.discountID == m.Id).ToArray();
            }

            return data;
        }

        public async Task<PromoteSalesDiscount[]> AttachWarehouse(PromoteSalesDiscount[] data)
        {
            var db = await this.promoteSalesDiscounts.GetDbContextAsync();

            await db.AttachData<Warehouse.Entity.Warehouse, PromoteSalesDiscount>(
                data, idGetter: x => x.warehouseID, action: (m, arr) =>
                {
                    m.Warehouse = arr.FirstOrDefault();
                });

            return data;
        }

        public async Task<PromoteSalesDiscount[]> AttachSingleGoods(PromoteSalesDiscount[] data)
        {
            var db = await this.promoteSalesDiscounts.GetDbContextAsync();

            var ids = data.Select(x => x.Id).ToArray();

            var discountMap = await db.Set<PromoteSalesDiscountGoods>().AsNoTracking().Where(x => ids.Contains(x.discountID)).ToArrayAsync();
            await db.AttachData<GoodsInfo, PromoteSalesDiscountGoods>(discountMap, x => x.goodsID, (m, arr) => m.GoodsInfo = arr.FirstOrDefault());

            foreach (var m in data)
            {
                m.PromoteSalesDiscountGoods = discountMap.Where(x => x.goodsID == m.Id).ToArray();
            }

            return data;
        }

        #region 单品打折商品导入
        /// <summary>
        /// 单品打折商品导入
        /// </summary>
        /// <param name="formFile"></param>
        /// <param name="discountId"></param>
        /// <param name="warehouseId"></param>
        /// <returns></returns>
        public async Task<(bool, string)> ImportDiscountGoods(IFormFile formFile, string discountId, string warehouseId)
        {
            if (formFile?.Length > 0)
            {
                var dt = DateTime.Now;
                //ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
                using (var package = new ExcelPackage(formFile.OpenReadStream()))
                {
                    var worksheet = package.Workbook.Worksheets[0];
                    int rowCount = worksheet.Dimension.Rows;
                    bool bHeaderRow = true;

                    var discountGoodsInfo = new List<DiscountGoodsImportDto>();
                    #region 加载Excel的数据到内存
                    for (int row = 1; row <= rowCount; row++)
                    {
                        if (bHeaderRow)
                        {
                            bHeaderRow = false;
                        }
                        else
                        {
                            var seqNo = worksheet.GetValue(row, 1)?.ToString();
                            var goodsNo = worksheet.GetValue(row, 2)?.ToString();
                            if (!object.Equals(goodsNo, null))
                                discountGoodsInfo.Add(new DiscountGoodsImportDto
                                {
                                    SeqNo = row - 1,
                                    GoodsNo = goodsNo
                                });
                        }
                    }
                    #endregion
                    //如果EXCEL加载的记录一条都没，那直接return
                    if (discountGoodsInfo.Count <= 0)
                        return (false, "excel没有记录");

                    var parallelTime = (DateTime.Now - dt).TotalMilliseconds;
                    Console.WriteLine($"读取Excel信息耗时{parallelTime}毫秒");
                    Log.Information($"读取Excel信息耗时{parallelTime}毫秒");

                    #region 校验数据正确性
                    dt = DateTime.Now;
                    //获取打折id
                    var db = await this.promoteSalesDiscounts.GetDbContextAsync();
                    var psDiscount = await db.Set<PromoteSalesDiscount>().AsNoTracking().Where(x => x.Id == discountId && x.isAuditing == true && x.isShow == true && x.discountType == 1 && x.warehouseID == warehouseId).FirstOrDefaultAsync();
                    if (object.Equals(psDiscount, null))
                        return (false, "打折订单不存在");

                    //获取商品
                    var strSql = @"SELECT gi.id goodsID, gi.barCode, gi.No, gi.goodsName
                                     FROM goods_info gi
                                   LEFT JOIN warehouse_goods wg ON gi.id = wg.goodsID
						            WHERE 1=1";
                    if (!string.IsNullOrWhiteSpace(warehouseId))
                        strSql = strSql + $@"         AND wg.warehouseID = '{warehouseId}'";
                    var resultRes = await _dapperRepository.GetListAsync<QueryAllGoodsByWarehouseResponse>(strSql);
                    //不在数据库记录里，但在传入的商品记录里面
                    var temps = discountGoodsInfo.Where(x => !resultRes.Any(y => y.No == x.GoodsNo))?.ToList();
                    if (temps != null && temps.Count > 0)
                        return (false, "有异常的商品记录！");

                    //不在数据库记录里，但在传入的商品记录里面
                    var discountGoodsDatas = resultRes.Where(x => discountGoodsInfo.Any(y => y.GoodsNo == x.No))?.ToList();

                    parallelTime = (DateTime.Now - dt).TotalMilliseconds;
                    Console.WriteLine($"检验信息耗时{parallelTime}毫秒");
                    Log.Information($"检验信息耗时{parallelTime}毫秒");
                    #endregion

                    #region 保存更新数据库
                    try
                    {
                        var saveRet = SaveImportDiscountGoodsInfo(discountGoodsDatas, discountId);
                        if (!string.IsNullOrWhiteSpace(saveRet.Item2))
                            return (false, saveRet.Item2);
                        else
                            return (true, "");
                    }
                    catch (Exception e)
                    {
                        Log.Error(e.Message);
                        return (false, "异常");
                    }
                    #endregion
                }
            }
            else
            {
                return (false, "请选择文件");
            }

        }
        #endregion

        #region 保存导入单品打折商品数据
        /// <summary>
        /// 保存导入单品打折商品数据
        /// </summary>
        /// <param name="discountGoodsDatas"></param>
        /// <param name="discountId"></param>
        /// <returns></returns>
        private (int, string) SaveImportDiscountGoodsInfo(List<QueryAllGoodsByWarehouseResponse> discountGoodsDatas, string discountId)
        {
            var result = (0, "");
            #region 保存数据            
            try
            {
                var dt = DateTime.Now;

                var trans = _dapperRepository.BeginTransactionDapper();

                //保存前先将相关数据删除
                var deleteOldSql = @$"DELETE FROM promote_sale_discount_goods WHERE discountID = '{discountId}';";
                _dapperRepository.Execute(deleteOldSql, dbTransaction: trans);

                var insertNewSql = @$"INSERT INTO promote_sale_discount_goods(id, addTime, updateTime, discountID, goodsID, isIntegral) 
                                                              VALUES (REPLACE(NEWID(),'-',''), GETDATE(), GETDATE(), '{discountId}', @goodsID, 1);";
                var resultDatas = _dapperRepository.Execute(insertNewSql, discountGoodsDatas, trans);


                var parallelTime = (DateTime.Now - dt).TotalMilliseconds;
                Console.WriteLine($"保存导入数据耗时{parallelTime}毫秒");
                Log.Information($"保存导入数据耗时{parallelTime}毫秒");

                _dapperRepository.CommitDapper();

                result = (resultDatas, "");
                return result;
            }
            catch (Exception e)
            {
                _dapperRepository?.RollbackDapper();
                result = (0, e.Message);
                return result;
            }
            #endregion
        }
        #endregion
    }
}
